package com.rtx.edi.platform.jobextend.service.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.rtx.api.annotation.DataSourceAnnotation;
import com.rtx.api.utils.FastJsonUtils;
import com.rtx.api.utils.MultipleDataSource;
import com.rtx.edi.model.api.po.PlApiMapping;
import com.rtx.edi.model.apibaseinfo.po.PlApibaseinfo;
import com.rtx.edi.platform.baseinfo.mapper.PlApibaseinfoMapper;
import com.rtx.edi.platform.jobextend.bean.bo.PlJobBo;
import com.rtx.edi.platform.jobextend.bean.dto.PlJobDTO;
import com.rtx.edi.platform.jobextend.bean.dto.PlJobMappingDTO;
import com.rtx.edi.platform.jobextend.bean.po.PlJob;
import com.rtx.edi.platform.jobextend.bean.po.PlJobDetail;
import com.rtx.edi.platform.jobextend.bean.po.PlJobMapping;
import com.rtx.edi.platform.jobextend.mapper.PlJobDetailMapper;
import com.rtx.edi.platform.jobextend.mapper.PlJobMapper;
import com.rtx.edi.platform.jobextend.mapper.PlJobMappingMapper;
import com.rtx.edi.platform.jobextend.service.PlJobMappingService;



@Service
public class PlJobMappingServiceImpl extends ServiceImpl<PlJobMappingMapper, PlJobMapping> implements PlJobMappingService{

	Logger logger = LoggerFactory.getLogger(this.getClass());
	
	@Autowired
	PlJobMapper plJobMapper;
	
	@Autowired
	PlJobDetailMapper plJobDetailMapper;
	
	@Autowired
	PlApibaseinfoMapper plApibaseinfoMapper;
	
	@Autowired
	PlJobMappingMapper plJobMappingMapper;
	
	@Resource(name = "wmwhse1")
	DataSource dataSource;
	
//	@Autowired
//	ApplicationContext appContext;
	
	
	/**
	 * 	更新&添加PlJobMapping
	 */
	@Override
	public void addPlJObMapping(PlJobDTO dto) {
		PlApiMapping po = new PlApiMapping();
		BeanUtils.copyProperties(dto, po);
		
		QueryWrapper<PlJobMapping> qws = new QueryWrapper<PlJobMapping>();
		qws.eq("job_id", dto.getId());
		List<PlJobMapping> list = plJobMappingMapper.selectList(qws);
		if(list.size() > 0) {
			plJobMappingMapper.delByJobId(dto.getId());
		}
		
		for(PlJobMappingDTO structureDTO:dto.getDetails()) {
			PlJobMapping structurePO = new PlJobMapping();
			BeanUtils.copyProperties(structureDTO, structurePO);
			structurePO.setCompanyId(1l);
			String sourceField = Optional.ofNullable(structureDTO.getSourceField()).orElse("");
			String fieldFullPath = Optional.ofNullable(structureDTO.getFieldFullPath()).orElse("");
			String[] sourceFieldArray = sourceField.split("\\.");
			structurePO.setTargetFieldPath(fieldFullPath.replace("root.", ""));
			structurePO.setSourceFieldPath(sourceField.replace("root.", ""));
			structurePO.setSourceFieldName(sourceFieldArray[sourceFieldArray.length-1]);
			plJobMappingMapper.insert(structurePO);
		}
	}
	
	
	
	@DataSourceAnnotation
	public Map<String,Object> getFieldMappingConfig(String datasourcekey, PlJobBo bo) throws Exception{
		Map<String,Object> res = new HashMap<String, Object>();
		//生成源数据json模板
		String sJson = getSourceJson(bo);
		//获取目标json
		String tJson = bo.getApiRequestTemplate();
		res.put("sTemplate", sJson);
		res.put("tTemplate", tJson);
		
		List<Map<String,String>> sSelectorList = new ArrayList<Map<String,String>>();
		List<Map<String, String>> sJsonList = FastJsonUtils.getFormatJSON(sJson);
		for(Map<String,String> tempEntity:sJsonList) {
			if(!StringUtils.hasLength(tempEntity.get("fieldType"))) {
				Map<String,String> sSelector = new HashMap<String, String>();
				sSelector.put("code", tempEntity.get("fieldFullPath"));
				sSelector.put("name", tempEntity.get("fieldFullPath"));
				sSelectorList.add(sSelector);
			}
		}
		
		List<PlJobMapping> mappingList =  bo.getJobMapping();
		if(mappingList.size() > 0) {
			List<PlJobMappingDTO> tempStructureList = new ArrayList<PlJobMappingDTO>();
			for(PlJobMapping structure:mappingList) {
				PlJobMappingDTO tempDTO = new PlJobMappingDTO();
				BeanUtils.copyProperties(structure, tempDTO);
				tempDTO.setSourceField(structure.getSourceFieldPath());
				tempStructureList.add(tempDTO);
			}
			res.put("targetStructureList", tempStructureList);
			res.put("sourceSelecter", sSelectorList);
		}else {
			List<Map<String, String>> tJsonList = FastJsonUtils.getFormatJSON(tJson);
			res.put("targetStructureList", tJsonList);
			res.put("sourceSelecter", sSelectorList);
		}
		
		return res;
	}
	
	
	/**
	 * 获取目标json
	 * @param jobId
	 * @return
	 */
	@DataSourceAnnotation
	public PlJobBo getJobConfig(String datasourcekey,Long jobId) {
		PlJobBo bo = new PlJobBo();
		PlJob po = plJobMapper.selectById(jobId);
		Long apiId = po.getTargetApiId();
		PlApibaseinfo api = plApibaseinfoMapper.selectById(apiId);
		bo.setApiRequestTemplate(api.getApiRequestTemplate());
		bo.setQuerySql(po.getQuerySql());
		
		QueryWrapper<PlJobDetail> qw = new QueryWrapper<PlJobDetail>();
		qw.eq("job_id", jobId);
		
		List<PlJobDetail> details = plJobDetailMapper.selectList(qw);
		bo.setJobDetail(details);
		
		QueryWrapper<PlJobMapping> qwm = new QueryWrapper<PlJobMapping>();
		qwm.eq("job_id", jobId);
		List<PlJobMapping> mappingList =  plJobMappingMapper.selectList(qwm);
		bo.setJobMapping(mappingList);
		
		bo.setJobId(jobId);
		return bo;
	}
	
	/**
	 * 根据配置信息生成 源json数据
	 * @param jobId
	 * @return
	 * @throws Exception
	 */
	private String getSourceJson(PlJobBo bo) throws Exception {
		Map<String,Object> configJSON = new HashMap<String, Object>();
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			
			Map<String,Object> root = new HashMap<String, Object>();
			//解析主sql获取查询字段
//			PlJob job = plJobMapper.selectById(jobId);
			String mainSql = bo.getQuerySql();
			Statement statement = conn.createStatement();
			ResultSet set = statement.executeQuery(mainSql);
			ResultSetMetaData metaData =  set.getMetaData();
			
			List<Map<String,Object>> maindataList = new ArrayList<Map<String,Object>>();
			Map<String,Object> maindata = new HashMap<String, Object>();
			int count = metaData.getColumnCount();
			for(int i=1;i<count+1;i++) {
				maindata.put(metaData.getColumnLabel(i), "");
			}
			maindataList.add(maindata);
			configJSON.put("mainData", maindataList);
			
			
			List<PlJobDetail> details = bo.getJobDetail();
			for(PlJobDetail jobdetail:details) {
				List<Map<String,Object>> subList = new ArrayList<>();
				Map<String,Object> subConfig = new HashMap<String, Object>();
				//明细sql的查询条件
				String queryconditionStr =  jobdetail.getQuerycondition(); 
				List<JSONObject> queryconditionList = JSON.parseArray(queryconditionStr, JSONObject.class);
				//明细sql
				String subsql = jobdetail.getSubSql();
				
				for(JSONObject querycondition:queryconditionList) {
					String replaceStr = "\\#\\{"+querycondition.getString("placeholder")+"\\}";
					String replaceValue ="''";
					subsql = subsql.replaceAll(replaceStr, replaceValue);
				}
				
				ResultSet subSet = statement.executeQuery(subsql);
				ResultSetMetaData subMetaData =  subSet.getMetaData();
				int subCount = subMetaData.getColumnCount();
				for(int i=1;i<subCount+1;i++) {
					subConfig.put(subMetaData.getColumnLabel(i), "");
				}
				
				subList.add(subConfig);
				maindata.put(jobdetail.getQueryName(), subList);
			}
		} catch (Exception e) {
			logger.error("获取sql字段异常",e);
		}finally {
			if(conn != null) {
				conn.close();
			}
		}
		return JSON.toJSONString(configJSON);
	}


	@Override
	public Map<String,List<Map<String,Object>>> executeSqlByJobId(String datasourceky,Long jobId){
		MultipleDataSource.setDataSourceKey("ris_edi_dev");
		PlJob job = plJobMapper.selectById(jobId);
		
		QueryWrapper<PlJobDetail> qw = new QueryWrapper<PlJobDetail>();
		qw.eq("job_id", jobId);
		qw.orderByAsc("sequence");
		List<PlJobDetail> details = plJobDetailMapper.selectList(qw);
		MultipleDataSource.ClearDataSourceKey();
		
		MultipleDataSource.setDataSourceKey(datasourceky);
		//执行查询sql后的全变量
		Map<String,List<Map<String,Object>>> executeSqlResult = new HashMap<String, List<Map<String,Object>>>();
		List<Map<String, Object>> maindataList = plJobMappingMapper.query(job.getQuerySql());
		for(Map<String,Object> mainData:maindataList) {
			for(PlJobDetail jobDetail:details) {
				String subSql = jobDetail.getSubSql();
				String queryName = jobDetail.getQueryName();
				String queryconditionStr = jobDetail.getQuerycondition();
				List<JSONObject> columnConfig = JSON.parseArray(queryconditionStr, JSONObject.class);
				for(JSONObject config:columnConfig) {
					String replaceStr = "\\#\\{"+config.getString("placeholder")+"\\}";
					String replaceValue ="'"+mainData.get(config.getString("column"))+"'";
					subSql = subSql.replaceAll(replaceStr, replaceValue);
				}
				List<Map<String,Object>> sublist = plJobMappingMapper.query(subSql);
				mainData.put(queryName, sublist);
			}
		}
		
		executeSqlResult.put("mainData", maindataList);
		
		return executeSqlResult;
	}
}
